package dao;

import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;

import util.C3P0Util;
import domain.Owner;
import domain.Pet;

public class PetDaoImpl {

	/**
	 * 查看所有宠物主人信息
	 * 
	 * @return
	 * @throws SQLException
	 */
	public List<Pet> findAllPets() throws SQLException {
		QueryRunner qr = new QueryRunner(C3P0Util.getDataSource());
		return qr.query("select * from pet",
				new BeanListHandler<Pet>(Pet.class));
	}

	/**
	 * 添加宠物信息
	 * 
	 * @param pet
	 * @throws SQLException
	 */
	public void addPet(Pet pet) throws SQLException {
		QueryRunner qr = new QueryRunner(C3P0Util.getDataSource());
		qr.update("INSERT INTO pet VALUES(?,?,?,?,?,?,?)", pet.getPetId(),
				pet.getPetName(), pet.getPetSpecies(), pet.getPetBirthday(),
				pet.getOwnerId(), pet.getDoctorId(), pet.getDescription());
	}

	/**
	 * 通过petId找到宠物信息
	 * 
	 * @param petId
	 * @return
	 * @throws SQLException
	 */
	public Pet findPetById(String petId) throws SQLException {
		QueryRunner qr = new QueryRunner(C3P0Util.getDataSource());
		return qr.query("select * from pet where petId=?",
				new BeanHandler<Pet>(Pet.class), petId);
	}

	/**
	 * 更新宠物信息
	 * 
	 * @param pet
	 * @throws SQLException
	 */
	public void updatePet(Pet pet) throws SQLException {
		QueryRunner qr = new QueryRunner(C3P0Util.getDataSource());
		qr.update(
				"update pet set petName=?,petSpecies=?,petBirthday=?,ownerId=?,doctorId=?,description=? where petId=?",
				pet.getPetName(), pet.getPetSpecies(), pet.getPetBirthday(),
				pet.getOwnerId(), pet.getDoctorId(), pet.getDescription(),
				pet.getPetId());

	}

	/**
	 * 通过petId删除宠物信息
	 * 
	 * @param petId
	 * @throws SQLException
	 */
	public void deletePetById(String petId) throws SQLException {
		QueryRunner qr = new QueryRunner(C3P0Util.getDataSource());
		qr.update("delete from pet where petId=?", petId);
	}

	/**
	 * 通过petId查找宠物主人信息
	 * 
	 * @param petId
	 * @throws SQLException
	 */
	public Owner findOwnerByPetId(String petId) throws SQLException {
		QueryRunner qr = new QueryRunner(C3P0Util.getDataSource());
		return qr
				.query("select * from owner where ownerId=(select ownerId from pet where petId=?)",
						new BeanHandler<Owner>(Owner.class), petId);
	}

	/**
	 * 
	 * @param petId
	 * @param petName
	 * @param category
	 * @param petBirthday
	 * @param ownerId
	 * @param doctorId
	 * @return
	 * @throws SQLException
	 */
	public List<Pet> findPetByConditions(String petId, String petName,
			String petSpecies, String petBirthday, String ownerId, String doctorId)
			throws SQLException {
		QueryRunner qr = new QueryRunner(C3P0Util.getDataSource());
		String sql = "select * from pet where 1=1";
		List list = new ArrayList();
		if(!"".equals(petId.trim())){
			sql+=" and petId like ?"; //  不能在这写%   %'1002'%
			list.add("%"+petId.trim()+"%");// '%1002%'
		}
		
		if(!"".equals(petName.trim())){
			sql+=" and petName like ?";
			list.add("%"+petName.trim()+"%");
		}
		
		if(!"".equals(petSpecies.trim())){
			sql+=" and petSpecies=?";
			list.add(petSpecies.trim());
		}
		
		if(!"".equals(petBirthday.trim())){
			sql+=" and petBirthday like ?";
			list.add("%"+petBirthday.trim()+"%");
		}
		if(!"".equals(ownerId.trim())){
			sql+=" and ownerId like ?";
			list.add("%"+ownerId.trim()+"%");
		}
		if(!"".equals(doctorId.trim())){
			sql+=" and doctorId like ?";
			list.add("%"+doctorId.trim()+"%");
		}
		
		return qr.query(sql, new BeanListHandler<Pet>(Pet.class),list.toArray());
	}

}
